﻿/*
'Description:转工生产看板看板(生产作业会)中间表结转
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Wangjh
'create Date:2022-11-10
'Rewriter:
'Rewrite Date:
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using UFC.DBUtility;
using System.Data;
using System.Collections;
using UFC.Common;
using System.Configuration;

namespace TransferMesReportForm
{
    //转工生产看板(生产作业会)
    class TransferShipBoard
    {
        private static string mesReportConn = ConfigurationManager.AppSettings["MesReportOracleConn"];
        //入口
        public void RunMain()
        {
            LogHelp.WriteLog("TransferShipBoard服务循环开始---------------------");
            System.Threading.Thread thAddData = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    AddData();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("TransferShipBoard服务-添加数据", e);
                }
            }));
            thAddData.Start();

            System.Threading.Thread thUpdateData = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    UpdateData();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("TransferShipBoard服务-更新数据", e);
                }
            }));
            thUpdateData.Start();

            System.Threading.Thread thdeleteData = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    deleteData();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("TransferShipBoard服务-删除数据", e);
                }
            }));
            thdeleteData.Start();

            System.Threading.Thread thdealInvalidData = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    dealInvalidData();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("TransferShipBoard服务-无效数据处理", e);
                }
            }));
            thdealInvalidData.Start();

            //线程等待
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thAddData.ThreadState != System.Threading.ThreadState.Stopped);
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thUpdateData.ThreadState != System.Threading.ThreadState.Stopped);
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thdeleteData.ThreadState != System.Threading.ThreadState.Stopped);
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thdealInvalidData.ThreadState != System.Threading.ThreadState.Stopped);

            LogHelp.WriteLog("TransferShipBoard服务循循环结束---------------------");
        }
        //增量数据
        public void AddData()
        {
            //查询需要结转的数据,排产表有，结转表没有的数据
            string strSql = @"SELECT cci.containername,ws.workflowstepname,wc.workcentername FROMWORKCENTERNAME,cci.productname,cci.productdescription,cp.stepplancreatedate,cp.stepplanshipdate,e.fullname JJPLANER,
e2.fullname RBPLANER,cp.PLANNEDSTARTDATE,cp.PLANNEDFINISHDATE,fa.factoryname,fa2.factoryname specfactoryname,cp.containerstepplanid  FROM containerstepplan cp
inner JOIN containercurrentinfo cci ON cci.containerid=cp.containerid
LEFT JOIN workflowstep ws ON ws.workflowstepid=cp.workflowstepid
LEFT JOIN factory fa ON fa.factoryid=cci.originalfactoryid--主制
LEFT JOIN workcenter wc ON wc.workcenterid=cci.fromworkcenterid--主制工区
LEFT JOIN factory fa2 ON fa2.factoryid=cp.specfactoryid
LEFT JOIN employee e ON e.employeeid=cp.stepplancreateemployeeid--机加排产人
LEFT JOIN employee e2 ON e2.employeeid=cp.stepplanshipemployeeid--承制排产人
LEFT JOIN r_shipboard_jijia@mesreport rs ON rs.containername=cci.containername AND rs.workflowstepname=ws.workflowstepname 
WHERE cp.stepplancreatedate>SYSDATE-365 and cci.containerstatus='1' and cp.plantype=2 and ws.workflowstepid is not null
AND (
rs.containername IS NULL OR
nvl(rs.plannedstartdate,SYSDATE)<>nvl(cp.plannedstartdate,SYSDATE) OR
nvl(rs.plannedfinishdate,SYSDATE)!=nvl(cp.plannedfinishdate,SYSDATE)
)
 AND ROWNUM<5000 ";
            var dt = OracleHelper.Query(strSql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                //先删除再插入
                string delSql = @"delete r_shipboard_jijia rs where rs.containername='{0}' and rs.workflowstepname='{1}'";
                //插入数据到中间表
                string insertSql = @"INSERT INTO r_shipboard_jijia rs (rs.containername,
rs.workflowstepname,
rs.FROMWORKCENTERNAME,
rs.productname,
rs.productdescription,
rs.stepplancreatedate,
rs.stepplanshipdate,
rs.JJPLANER,
rs.RBPLANER,
PLANNEDSTARTDATE,
PLANNEDFINISHDATE,
factoryname,
tofactoryname) 
VALUES ('{0}','{1}','{2}','{3}','{4}',to_date('{5}','yyyy/mm/dd hh24:mi:ss'),to_date('{6}','yyyy/mm/dd hh24:mi:ss'),'{7}','{8}',
to_date('{9}','yyyy/mm/dd hh24:mi:ss'),
to_date('{10}','yyyy/mm/dd hh24:mi:ss'),'{11}','{12}')";
                var inserSqlList = new ArrayList();
                foreach (DataRow dr in dt.Rows)
                {
                    inserSqlList.Add(string.Format(delSql, dr["containername"].ToString(), dr["workflowstepname"].ToString()));
                    inserSqlList.Add(string.Format(insertSql,
                        dr["containername"].ToString(),
                        dr["workflowstepname"].ToString(),
                        dr["FROMWORKCENTERNAME"].ToString(),
                        dr["productname"].ToString(),
                        dr["productdescription"].ToString(),
                        dr["stepplancreatedate"].ToString(),
                        dr["stepplanshipdate"].ToString(),
                        dr["JJPLANER"].ToString(),
                        dr["RBPLANER"].ToString(),
                        dr["PLANNEDSTARTDATE"].ToString(),
                        dr["PLANNEDFINISHDATE"].ToString(),
                        dr["factoryname"].ToString(),
                        dr["specfactoryname"].ToString()));
                }
                OracleHelper.ExecuteSqlTran(inserSqlList, mesReportConn);
                LogHelp.WriteLog("TransferShipBoard服务-添加中间表r_shipboard_jijia数据条数：" + inserSqlList.Count / 2);
            }
        }

        //删除数据
        public void deleteData()
        {
            //yuxj20221117 插入只插入状态=1 及删除不等于1的数据
            string strSql = @"
SELECT rs.* FROM r_shipboard_jijia@mesreport rs
LEFT JOIN (     
SELECT cci.containername,cp.workflowstepname FROM containerstepplan cp
LEFT JOIN containercurrentinfo cci ON cci.containerid=cp.containerid
WHERE cp.plantype='2' and cci.containerstatus='1' 
) a ON a.containername=rs.containername AND a.workflowstepname=rs.workflowstepname
WHERE rs.plannedfinishdate>=SYSDATE-365 AND a.containername IS NULL ";

            string delSql = @"delete r_shipboard_jijia rs where rs.containername='{0}' and rs.workflowstepname='{1}'";
            var dt = OracleHelper.Query(strSql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                var delSqlList = new ArrayList();
                foreach (DataRow dr in dt.Rows)
                {
                    delSqlList.Add(string.Format(delSql, dr["containername"].ToString(), dr["workflowstepname"].ToString()));
                }
                OracleHelper.ExecuteSqlTran(delSqlList, mesReportConn);
                LogHelp.WriteLog("TransferShipBoard服务-删除中间表r_shipboard_jijia数据条数：" + delSqlList.Count);
            }
        }

        //更新数据，更新转工业务数据
        public void UpdateData()
        {
            string strSql = @"SELECT cci.containername,ws.workflowstepname,csr.SENDDATE,e.fullname sender,csr.toacceptdate RECIVEDATE,e2.fullname RECEIVER,
to_char(csr.needcompletedate,'yyyy/mm/dd') needcompletedate,to_char(csr.estimatedcompletedate,'yyyy/mm/dd') estimatedcompletedate,
csre.completedate,e3.fullname completeName,csre.recivedate SHIPINDATE,e4.fullname SHIPINname,pdl.createdate DENYDATE,pdl.reasonnote DENYREASON,e5.fullname DENYER
,TRUNC(csr.toacceptdate) - TRUNC(rs.plannedstartdate) SENDOUTDELAY,TRUNC(rs.plannedstartdate)-TRUNC(csr.toacceptdate) PRESEND,fa.factoryname TOFACTORYNAME,
wc.workcentername TOWORKCENTERNAME,cci.qty wipqty,csre.acceptanceqty SHIPACCEPTANCEQTY,csre.lossqty SHIPLOSSQTY,csre.completeqty SHIPCOMPLETEQTY,csr.containershiprecordid
  FROM containershiprecord csr--转工表
LEFT JOIN containershiprecive csre ON csre.containershiprecordid=csr.containershiprecordid AND csre.isuse=0 AND csre.containerid=csr.containerid
LEFT JOIN containercurrentinfo cci ON cci.containerid=csr.containerid
LEFT JOIN containershiprecordstep csrs ON csrs.containershiprecordid=csr.containershiprecordid
LEFT JOIN workflowstep ws ON ws.workflowstepid=csrs.workflowstepid
LEFT JOIN employee e ON e.employeeid=csr.sender--转出人
LEFT JOIN employee e2 ON e2.employeeid=csr.toacceptor--接收人
LEFT JOIN employee e3 ON e3.employeeid=csre.employeeid--完成人
LEFT JOIN employee e4 ON e4.employeeid=csre.reciver--转入人
LEFT JOIN processDealRecord pdl ON pdl.parentid=csr.containershiprecordid AND pdl.processtype IN (1,4)
LEFT JOIN employee e5 ON e5.employeeid=pdl.employeeid--拒收人
LEFT JOIN factory fa ON fa.factoryid=csr.tofactoryid--承制车间
LEFT JOIN workcenter wc ON wc.workcenterid=csr.toworkcenterid--承制工区

INNER JOIN r_shipboard_jijia@mesreport rs ON rs.containername=cci.containername AND rs.workflowstepname=ws.workflowstepname--结转表
WHERE csr.isuse=0 AND (

nvl(rs.RECIVEDATE,SYSDATE)<>NVL(csr.toacceptdate,SYSDATE) OR
nvl(rs.SENDDATE,SYSDATE)<>NVL(csr.senddate,SYSDATE) OR
nvl(rs.NEEDCOMPLETEDATE,SYSDATE)<>NVL(csr.needcompletedate,SYSDATE) OR
nvl(rs.ESTIMATEDCOMPLETEDATE,SYSDATE)<>NVL(csr.ESTIMATEDCOMPLETEDATE,SYSDATE) OR
nvl(rs.COMPLETEDATE,SYSDATE)<>NVL(csre.completedate,SYSDATE) OR
nvl(rs.SHIPINDATE,SYSDATE)<>NVL(csre.recivedate,SYSDATE) 

)  
--AND cci.containername='7206057/2003' 
AND ROWNUM<5000 ";
            var dt = OracleHelper.Query(strSql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                string updateSql = @"UPDATE r_shipboard_jijia rs 
SET rs.RECEIVER='{0}',
RECIVEDATE=to_date('{1}','yyyy/mm/dd hh24:mi:ss'),
SENDDATE=to_date('{2}','yyyy/mm/dd hh24:mi:ss'),
NEEDCOMPLETEDATE=to_date('{3}','yyyy/mm/dd'),
ESTIMATEDCOMPLETEDATE=to_date('{4}','yyyy/mm/dd'),
COMPLETEDATE=to_date('{5}','yyyy/mm/dd hh24:mi:ss'),
SHIPINDATE=to_date('{6}','yyyy/mm/dd hh24:mi:ss'),
DENYDATE=to_date('{7}','yyyy/mm/dd hh24:mi:ss'),
DENYER='{8}',
DENYREASON='{9}',
SENDOUTDELAY='{10}',
PRESEND='{11}',
TOFACTORYNAME='{12}',
TOWORKCENTERNAME='{13}',
WIPQTY='{14}',
SHIPACCEPTANCEQTY='{15}',
SHIPLOSSQTY='{16}',
SHIPCOMPLETEQTY='{17}' 
WHERE rs.containername='{18}' AND rs.workflowstepname='{19}'";
                var updateSqlList = new ArrayList();
                foreach (DataRow dr in dt.Rows)
                {
                    updateSqlList.Add(string.Format(updateSql, dr["RECEIVER"].ToString(), dr["RECIVEDATE"].ToString(), dr["SENDDATE"].ToString(), dr["NEEDCOMPLETEDATE"].ToString(), dr["ESTIMATEDCOMPLETEDATE"].ToString(),
                        dr["COMPLETEDATE"].ToString(), dr["SHIPINDATE"].ToString(), dr["DENYDATE"].ToString(), dr["DENYER"].ToString(), dr["DENYREASON"].ToString(), dr["SENDOUTDELAY"].ToString(), dr["PRESEND"].ToString(),
                        dr["TOFACTORYNAME"].ToString(), dr["TOWORKCENTERNAME"].ToString(), dr["WIPQTY"].ToString(), dr["SHIPACCEPTANCEQTY"].ToString(), dr["SHIPLOSSQTY"].ToString(), dr["SHIPCOMPLETEQTY"].ToString(),
                        dr["containername"].ToString(), dr["workflowstepname"].ToString()));
                }
                OracleHelper.ExecuteSqlTran(updateSqlList, mesReportConn);
                LogHelp.WriteLog("TransferShipBoard服务-更新中间表r_shipboard_jijia数据条数：" + updateSqlList.Count);
            }
        }

        //处理特殊数据
        public void dealInvalidData()
        {
            //清除已删除批次的排产数据
            string strSql = @"SELECT cp.* FROM containerstepplan cp
WHERE NOT EXISTS (SELECT 1 FROM containercurrentinfo cci WHERE cci.containerid=cp.containerid) and rownum<5000";
            var dt = OracleHelper.Query(strSql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                var delSqlList = new ArrayList();
                foreach (DataRow dr in dt.Rows)
                {
                    delSqlList.Add(string.Format("delete containerstepplan cp where cp.containerid='{0}'", dr["containerid"].ToString()));
                }
                OracleHelper.ExecuteSqlTran(delSqlList);
                LogHelp.WriteLog("TransferShipBoard服务-无效排产数据-删除containerstepplan数据条数：" + delSqlList.Count);
            }

            //清除多个版本数据情况下，非有效版本的数据
            strSql = @"
SELECT  cp.* FROM 
(
SELECT * FROM (
SELECT DECODE(cci.isinstore,-1,c.mfgworkflowid,0,cci.workflowid,1,cci.bsworkflowid) realworkflowid,cci.isinstore,cci.containerid,c.mfgworkflowid,cci.bsworkflowid,cci.workflowid,cci.containername,ws.workflowstepname FROM containerstepplan cp
INNER JOIN containercurrentinfo cci ON cci.containerid=cp.containerid
LEFT JOIN container c ON c.containerid=cci.containerid
LEFT JOIN workflowstep ws ON ws.workflowstepid=cp.workflowstepid
WHERE cp.stepplancreatedate>SYSDATE-365 --cci.containername='S12A.06.1190/2207005' and 
GROUP BY DECODE(cci.isinstore,-1,c.mfgworkflowid,0,cci.workflowid,1,cci.bsworkflowid),cci.isinstore,cci.containerid,c.mfgworkflowid,cci.bsworkflowid,cci.workflowid,cci.containername,ws.workflowstepname
HAVING COUNT(ws.workflowstepname)>1
) WHERE  ROWNUM<1000
) c
INNER JOIN containerstepplan cp ON cp.containerid=c.containerid
LEFT JOIN workflowstep ws ON ws.workflowstepid=cp.workflowstepid AND ws.workflowstepname=c.workflowstepname

WHERE ws.workflowid!=c.realworkflowid  ";
            dt = OracleHelper.Query(strSql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                var sqlList = new ArrayList();
                var sqlList2 = new ArrayList();
                foreach (DataRow dr in dt.Rows)
                {
                    sqlList.Add(string.Format(@"delete CONTAINERSTEPPLAN_BAK where containerstepplanid='{0}'", dr["containerstepplanid"].ToString()));
                    sqlList.Add(string.Format(@"INSERT INTO CONTAINERSTEPPLAN_BAK SELECT * FROM containerstepplan WHERE containerstepplanid='{0}'", dr["containerstepplanid"].ToString()));
                    sqlList2.Add(string.Format(@"delete containerstepplan where containerstepplanid='{0}'", dr["containerstepplanid"].ToString()));
                }
                OracleHelper.ExecuteSqlTran(sqlList);
                OracleHelper.ExecuteSqlTran(sqlList2);
                LogHelp.WriteLog("TransferShipBoard服务-无效排产数据-删除containerstepplan旧版本数据条数：" + sqlList2.Count);
            }
        }

        //补齐部分字段
        void buqistepname()
        {
            //补齐specfactoryid,workflowstepname
            string strSql = @"SELECT cp.containerstepplanid,ws.workflowstepname,s.factoryid specfactoryid FROM containerstepplan cp
LEFT JOIN workflowstep ws ON ws.workflowstepid=cp.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
WHERE cp.stepplancreatedate>sysdate-365   AND s.factoryid IS NOT NULL
 AND (cp.specfactoryid IS NULL OR cp.workflowstepname IS NULL) AND ROWNUM<10000";
            var dt = OracleHelper.Query(strSql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                var updateSqlList = new ArrayList();
                foreach (DataRow dr in dt.Rows)
                {
                    updateSqlList.Add(string.Format("UPDATE  containerstepplan cp SET cp.workflowstepname='{0}',cp.specfactoryid='{1}' WHERE cp.containerstepplanid='{2}'",
                        dr["workflowstepname"].ToString(), dr["specfactoryid"].ToString(), dr["containerstepplanid"].ToString()));
                }
                OracleHelper.ExecuteSqlTran(updateSqlList);
                LogHelp.WriteLog("TransferShipBoard服务-补齐containerstepplan的workflowstepname和specfactoryid数据条数：" + updateSqlList.Count);
            }
        }

        /// <summary>
        /// 重复数据处理,containerstepplan同批次，工序有重复记录，只保留一份
        /// </summary>
        void repeatDeal()
        {
            string strSql = @"SELECT cp.* FROM (

SELECT DECODE(cci.isinstore,-1,c.mfgworkflowid,0,cci.workflowid,1,cci.bsworkflowid) realworkflowid,cci.isinstore,cci.containerid,c.mfgworkflowid,cci.bsworkflowid,cci.workflowid,cci.containername,ws.workflowstepname,MAX(cp.stepplancreatedate) maxcreatdate FROM containerstepplan cp
INNER JOIN containercurrentinfo cci ON cci.containerid=cp.containerid
LEFT JOIN container c ON c.containerid=cci.containerid
LEFT JOIN workflowstep ws ON ws.workflowstepid=cp.workflowstepid
WHERE cp.stepplancreatedate>SYSDATE-360  --and cci.containername='S10C.52.1801子工艺(0085)/2107005' and ws.workflowstepname='0015-酸洗'
GROUP BY DECODE(cci.isinstore,-1,c.mfgworkflowid,0,cci.workflowid,1,cci.bsworkflowid),cci.isinstore,cci.containerid,c.mfgworkflowid,cci.bsworkflowid,cci.workflowid,cci.containername,ws.workflowstepname
HAVING COUNT(ws.workflowstepname)>1

) m
INNER JOIN  containerstepplan cp ON cp.containerid=m.containerid
INNER JOIN workflowstep ws ON ws.workflowstepid=cp.workflowstepid AND ws.workflowstepname=m.workflowstepname
 
WHERE cp.stepplancreatedate<>m.maxcreatdate and rownum<5000 ";
            var dt = OracleHelper.Query(strSql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                var delSqlList = new ArrayList();
                foreach (DataRow dr in dt.Rows)
                {
                    delSqlList.Add(string.Format(@"INSERT INTO CONTAINERSTEPPLAN_BAK SELECT * FROM containerstepplan WHERE containerstepplanid='{0}'", dr["containerstepplanid"].ToString()));
                    delSqlList.Add(string.Format("delete containerstepplan cp where cp.containerstepplanid='{0}'", dr["containerstepplanid"].ToString()));
                }

                OracleHelper.ExecuteSqlTran(delSqlList);
                LogHelp.WriteLog("TransferShipBoard服务-无效排产数据-删除containerstepplan重复数据条数：" + delSqlList.Count / 2);
            }
        }
    }
}
